# coding=utf-8

from flask import jsonify, request

from blues.cloud import bp
from database import session_maker

sql_ecs_usage = """
# 求时间范围内ECS实例的CPU 内存 磁盘 使用率
SELECT
	t1.instance_id,
	t1.cpu_usage,
	t1.mem_usage,
	t2.disk_usage_all 
FROM
	(
	SELECT
		a.instance_id,
		ROUND( SUM( a.cpu * b.cpu_avg ) / SUM( a.cpu ), 2 ) AS cpu_usage,
		ROUND( SUM( a.memory * b.mem_avg ) / SUM( a.memory ), 2 ) AS mem_usage 
	FROM
		c_ecs_info a,
		cms_ecs b 
	WHERE
		a.instance_id = b.instance_id 
		AND DATE_FORMAT( a.record_time, '%Y-%m-%d' ) = b.date 
		AND b.date BETWEEN '{}' # 2023-09-01
		AND '{}' # 2023-09-30
	GROUP BY
		instance_id 
	) t1 LEFT JOIN
	(
	SELECT
		instance_id,
		ROUND( SUM( disk_size * disk_usage ) / sum( disk_size ), 2 ) AS disk_usage_all 
	FROM
		cms_ecs_disk 
	WHERE
		date BETWEEN '{}' # 2023-09-01
		AND '{}' #  2023-09-30
	GROUP BY
		instance_id 
	) t2 
ON
	t1.instance_id = t2.instance_id;
"""


def caculate_ecs_stats(start, end) -> dict:
    """
    生成并返回一个字典，key为ECS实例id，value是cpu mem disk的info和使用率数据
    :param start:
    :param end:
    :return:
    """
    ecs_stats_dt: dict = {}
    with session_maker() as session:
        # 记录ECS cpu、内存信息，cpu、内存使用率
        rs_ecs = session.execute(sql_ecs_usage.format(start, end, start, end))
        for row in rs_ecs:
            # ins_id = row[0]
            # cpu_usage = row[1]
            # mem_usage = row[2]
            # disk_usage = row[3]
            ecs_stats_dt[row[0]] = {
                'cpu_usage': row[1], 'mem_usage': row[2], 'disk_usage': row[3]
            }
        print(ecs_stats_dt)
    return ecs_stats_dt


@bp.route('/api/v1/cloud/get_ecs_stats', methods=['POST'])
def caculate_ecs_stats_api():
    """
    获取ECS监控统计数据（实例维度）API
    :param start: 查询开始时间，例如: '2023-09-15'
    :param end: 查询结束时间，例如: '2023-09-20'
    :return: 返回Json Http响应
    """
    start = request.args.get('start')  # "2023-09-21"
    end = request.args.get('end')  # "2023-09-24"
    data = caculate_ecs_stats(start, end)
    if not data:
        return jsonify({'code': 500})
    return jsonify({'code': 200, 'data': data})


sql_resource = """
SELECT
	id,
	org,
	project,
	res_type,
	instance_id,
	ip_private,
	ip_public,
	cpu,
	mem,
	sys_disk,
	data_disk,
	system_version,
	res_function,
	COMMENT,
	is_deleted 
FROM
	cmdb_cloud_resource 
WHERE
	is_deleted IS NULL 
ORDER BY
	org,
	project,
	res_type;
"""


def get_cloud_resource(start, end) -> list:
    res_li: list = []
    usage_dt: dict = caculate_ecs_stats(start, end)
    with session_maker() as session:
        # 记录ECS cpu、内存信息，cpu、内存使用率
        rs = session.execute(sql_resource)
        for row in rs:
            ins_id = row[4]
            tmp_ins_dt = {
                'org': row[1], 'project': row[2], 'res_type': row[3], 'instance_id': ins_id,
                'ip_private': row[5], 'ip_public': row[6], 'cpu': row[7], 'mem': row[8], 'sys_disk': row[9],
                'data_disk': row[10], 'system_version': row[11], 'res_function': row[12], 'comment': row[13],
            }
            if row[3] == 'ECS' and ins_id in usage_dt.keys():
                tmp_ins_dt['cpu_usage'] = usage_dt[ins_id]['cpu_usage']
                tmp_ins_dt['mem_usage'] = usage_dt[ins_id]['mem_usage']
                tmp_ins_dt['disk_usage'] = usage_dt[ins_id]['disk_usage']


            res_li.append(tmp_ins_dt)
    return res_li


@bp.route('/api/v1/cloud/resource', methods=['GET'])
def get_cloud_resource_api():
    """
    获取ECS监控统计数据（实例维度）API
    :param start: 查询开始时间，例如: '2023-09-15'
    :param end: 查询结束时间，例如: '2023-09-20'
    :return: 返回Json Http响应
    """
    start = request.args.get('start')  # "2023-09-21"
    end = request.args.get('end')  # "2023-09-24"
    data = get_cloud_resource(start, end)
    if not data:
        return jsonify({'code': 500})
    return jsonify({'code': 200, 'data': data})
